malloy logo Malloy Documentation
search

Shape Maps

The plugin currently supports US maps and state names. The model and data styles for the subsequent examples are:

source: airports is table('malloy-data.faa.airports') {
  primary_key: code
  measure: airport_count is count()
  query: by_state is {
    where: state != null
    group_by: state
    aggregate: airport_count
  }
}

Data Styles

{
  "by_state": {
    "renderer": "shape_map"
  }
}

Run as a simple query

query: airports -> { nest: by_state }
QUERY RESULTS
by_​state
31,845airport_count
[
  {
    "by_state": [
      {
        "state": "TX",
        "airport_count": 1845
      },
      {
        "state": "CA",
        "airport_count": 984
      },
      {
        "state": "IL",
        "airport_count": 890
      },
      {
        "state": "FL",
        "airport_count": 856
      },
      {
        "state": "PA",
        "airport_count": 804
      },
      {
        "state": "OH",
        "airport_count": 749
      },
      {
        "state": "IN",
        "airport_count": 643
      },
      {
        "state": "AK",
        "airport_count": 608
      },
      {
        "state": "NY",
        "airport_count": 576
      },
      {
        "state": "WI",
        "airport_count": 543
      },
      {
        "state": "MO",
        "airport_count": 537
      },
      {
        "state": "MN",
        "airport_count": 507
      },
      {
        "state": "LA",
        "airport_count": 500
      },
      {
        "state": "MI",
        "airport_count": 489
      },
      {
        "state": "WA",
        "airport_count": 484
      },
      {
        "state": "OK",
        "airport_count": 443
      },
      {
        "state": "OR",
        "airport_count": 441
      },
      {
        "state": "GA",
        "airport_count": 440
      },
      {
        "state": "ND",
        "airport_count": 436
      },
      {
        "state": "CO",
        "airport_count": 425
      },
      {
        "state": "VA",
        "airport_count": 421
      },
      {
        "state": "KS",
        "airport_count": 415
      },
      {
        "state": "NC",
        "airport_count": 400
      },
      {
        "state": "NJ",
        "airport_count": 378
      },
      {
        "state": "AZ",
        "airport_count": 319
      },
      {
        "state": "IA",
        "airport_count": 319
      },
      {
        "state": "NE",
        "airport_count": 308
      },
      {
        "state": "AR",
        "airport_count": 299
      },
      {
        "state": "TN",
        "airport_count": 285
      },
      {
        "state": "AL",
        "airport_count": 260
      },
      {
        "state": "MT",
        "airport_count": 259
      },
      {
        "state": "MS",
        "airport_count": 243
      },
      {
        "state": "ID",
        "airport_count": 238
      },
      {
        "state": "MD",
        "airport_count": 229
      },
      {
        "state": "MA",
        "airport_count": 225
      },
      {
        "state": "KY",
        "airport_count": 202
      },
      {
        "state": "SC",
        "airport_count": 189
      },
      {
        "state": "NM",
        "airport_count": 181
      },
      {
        "state": "SD",
        "airport_count": 180
      },
      {
        "state": "ME",
        "airport_count": 164
      },
      {
        "state": "CT",
        "airport_count": 153
      },
      {
        "state": "UT",
        "airport_count": 140
      },
      {
        "state": "NV",
        "airport_count": 128
      },
      {
        "state": "WV",
        "airport_count": 116
      },
      {
        "state": "WY",
        "airport_count": 115
      },
      {
        "state": "NH",
        "airport_count": 112
      },
      {
        "state": "VT",
        "airport_count": 81
      },
      {
        "state": "HI",
        "airport_count": 52
      },
      {
        "state": "DE",
        "airport_count": 42
      },
      {
        "state": "PR",
        "airport_count": 40
      },
      {
        "state": "RI",
        "airport_count": 28
      },
      {
        "state": "DC",
        "airport_count": 20
      },
      {
        "state": "CQ",
        "airport_count": 11
      },
      {
        "state": "VI",
        "airport_count": 9
      },
      {
        "state": "AS",
        "airport_count": 4
      },
      {
        "state": "GU",
        "airport_count": 3
      },
      {
        "state": "MQ",
        "airport_count": 1
      },
      {
        "state": "WQ",
        "airport_count": 1
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set=1 THEN
      airports.state
      END as state__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,1,1)))
  WHERE (group_set NOT IN (1) OR (group_set IN (1) AND airports.state IS NOT NULL))
  GROUP BY 1,2
)
SELECT
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    state__1 as state, 
    airport_count__1 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as by_state
FROM __stage0

Run as a trellis

By calling the configured map as a nested subtable, a trellis is formed.

query: airports -> {
  group_by: faa_region
  aggregate: airport_count
  nest: by_state
}
QUERY RESULTS
faa_​regionairport_​countby_​state
AGL4,437
180890airport_count
ASW3,268
1811,845airport_count
ASO2,924
9856airport_count
AEA2,586
20804airport_count
ANM2,102
115484airport_count
[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "by_state": [
      {
        "state": "IL",
        "airport_count": 890
      },
      {
        "state": "OH",
        "airport_count": 749
      },
      {
        "state": "IN",
        "airport_count": 643
      },
      {
        "state": "WI",
        "airport_count": 543
      },
      {
        "state": "MN",
        "airport_count": 507
      },
      {
        "state": "MI",
        "airport_count": 489
      },
      {
        "state": "ND",
        "airport_count": 436
      },
      {
        "state": "SD",
        "airport_count": 180
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "by_state": [
      {
        "state": "TX",
        "airport_count": 1845
      },
      {
        "state": "LA",
        "airport_count": 500
      },
      {
        "state": "OK",
        "airport_count": 443
      },
      {
        "state": "AR",
        "airport_count": 299
      },
      {
        "state": "NM",
        "airport_count": 181
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "by_state": [
      {
        "state": "FL",
        "airport_count": 856
      },
      {
        "state": "GA",
        "airport_count": 440
      },
      {
        "state": "NC",
        "airport_count": 400
      },
      {
        "state": "TN",
        "airport_count": 285
      },
      {
        "state": "AL",
        "airport_count": 260
      },
      {
        "state": "MS",
        "airport_count": 243
      },
      {
        "state": "KY",
        "airport_count": 202
      },
      {
        "state": "SC",
        "airport_count": 189
      },
      {
        "state": "PR",
        "airport_count": 40
      },
      {
        "state": "VI",
        "airport_count": 9
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "by_state": [
      {
        "state": "PA",
        "airport_count": 804
      },
      {
        "state": "NY",
        "airport_count": 576
      },
      {
        "state": "VA",
        "airport_count": 421
      },
      {
        "state": "NJ",
        "airport_count": 378
      },
      {
        "state": "MD",
        "airport_count": 229
      },
      {
        "state": "WV",
        "airport_count": 116
      },
      {
        "state": "DE",
        "airport_count": 42
      },
      {
        "state": "DC",
        "airport_count": 20
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "by_state": [
      {
        "state": "WA",
        "airport_count": 484
      },
      {
        "state": "OR",
        "airport_count": 441
      },
      {
        "state": "CO",
        "airport_count": 425
      },
      {
        "state": "MT",
        "airport_count": 259
      },
      {
        "state": "ID",
        "airport_count": 238
      },
      {
        "state": "UT",
        "airport_count": 140
      },
      {
        "state": "WY",
        "airport_count": 115
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1) THEN
      airports.faa_region
      END as faa_region__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set=1 THEN
      airports.state
      END as state__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,1,1)))
  WHERE (group_set NOT IN (1) OR (group_set IN (1) AND airports.state IS NOT NULL))
  GROUP BY 1,2,4
)
SELECT
  faa_region__0 as faa_region,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    state__1 as state, 
    airport_count__1 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as by_state
FROM __stage0
GROUP BY 1
ORDER BY 2 desc

Run as a trellis, repeated with different filters

query: airports -> {
  group_by: faa_region
  aggregate: airport_count
  nest:
    heliports is by_state { where: fac_type = 'HELIPORT' }
    seaplane_bases is by_state { where: fac_type = 'SEAPLANE BASE' }
}
QUERY RESULTS
faa_​regionairport_​countheliportsseaplane_​bases
AGL4,437
16245airport_count
172airport_count
ASW3,268
25435airport_count
117airport_count
ASO2,924
4280airport_count
143airport_count
AEA2,586
13307airport_count
123airport_count
ANM2,102
50100150airport_count
51015airport_count
[
  {
    "faa_region": "AGL",
    "airport_count": 4437,
    "heliports": [
      {
        "state": "IL",
        "airport_count": 245
      },
      {
        "state": "OH",
        "airport_count": 201
      },
      {
        "state": "IN",
        "airport_count": 115
      },
      {
        "state": "MI",
        "airport_count": 87
      },
      {
        "state": "WI",
        "airport_count": 85
      },
      {
        "state": "MN",
        "airport_count": 51
      },
      {
        "state": "SD",
        "airport_count": 26
      },
      {
        "state": "ND",
        "airport_count": 16
      }
    ],
    "seaplane_bases": [
      {
        "state": "MN",
        "airport_count": 72
      },
      {
        "state": "WI",
        "airport_count": 16
      },
      {
        "state": "IN",
        "airport_count": 12
      },
      {
        "state": "IL",
        "airport_count": 8
      },
      {
        "state": "MI",
        "airport_count": 7
      },
      {
        "state": "OH",
        "airport_count": 2
      },
      {
        "state": "SD",
        "airport_count": 1
      },
      {
        "state": "ND",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ASW",
    "airport_count": 3268,
    "heliports": [
      {
        "state": "TX",
        "airport_count": 435
      },
      {
        "state": "LA",
        "airport_count": 229
      },
      {
        "state": "OK",
        "airport_count": 92
      },
      {
        "state": "AR",
        "airport_count": 80
      },
      {
        "state": "NM",
        "airport_count": 25
      }
    ],
    "seaplane_bases": [
      {
        "state": "LA",
        "airport_count": 17
      },
      {
        "state": "OK",
        "airport_count": 1
      },
      {
        "state": "NM",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ASO",
    "airport_count": 2924,
    "heliports": [
      {
        "state": "FL",
        "airport_count": 280
      },
      {
        "state": "GA",
        "airport_count": 103
      },
      {
        "state": "TN",
        "airport_count": 87
      },
      {
        "state": "AL",
        "airport_count": 75
      },
      {
        "state": "NC",
        "airport_count": 70
      },
      {
        "state": "KY",
        "airport_count": 55
      },
      {
        "state": "MS",
        "airport_count": 49
      },
      {
        "state": "SC",
        "airport_count": 27
      },
      {
        "state": "PR",
        "airport_count": 20
      },
      {
        "state": "VI",
        "airport_count": 4
      }
    ],
    "seaplane_bases": [
      {
        "state": "FL",
        "airport_count": 43
      },
      {
        "state": "AL",
        "airport_count": 3
      },
      {
        "state": "VI",
        "airport_count": 3
      },
      {
        "state": "TN",
        "airport_count": 2
      },
      {
        "state": "SC",
        "airport_count": 2
      },
      {
        "state": "PR",
        "airport_count": 1
      },
      {
        "state": "NC",
        "airport_count": 1
      },
      {
        "state": "GA",
        "airport_count": 1
      },
      {
        "state": "KY",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "AEA",
    "airport_count": 2586,
    "heliports": [
      {
        "state": "PA",
        "airport_count": 307
      },
      {
        "state": "NJ",
        "airport_count": 247
      },
      {
        "state": "NY",
        "airport_count": 156
      },
      {
        "state": "VA",
        "airport_count": 126
      },
      {
        "state": "MD",
        "airport_count": 64
      },
      {
        "state": "WV",
        "airport_count": 33
      },
      {
        "state": "DC",
        "airport_count": 18
      },
      {
        "state": "DE",
        "airport_count": 13
      }
    ],
    "seaplane_bases": [
      {
        "state": "NY",
        "airport_count": 23
      },
      {
        "state": "NJ",
        "airport_count": 10
      },
      {
        "state": "PA",
        "airport_count": 10
      },
      {
        "state": "WV",
        "airport_count": 10
      },
      {
        "state": "VA",
        "airport_count": 4
      },
      {
        "state": "MD",
        "airport_count": 3
      },
      {
        "state": "DE",
        "airport_count": 1
      }
    ]
  },
  {
    "faa_region": "ANM",
    "airport_count": 2102,
    "heliports": [
      {
        "state": "CO",
        "airport_count": 165
      },
      {
        "state": "WA",
        "airport_count": 130
      },
      {
        "state": "OR",
        "airport_count": 100
      },
      {
        "state": "UT",
        "airport_count": 43
      },
      {
        "state": "ID",
        "airport_count": 36
      },
      {
        "state": "MT",
        "airport_count": 29
      },
      {
        "state": "WY",
        "airport_count": 24
      }
    ],
    "seaplane_bases": [
      {
        "state": "WA",
        "airport_count": 15
      },
      {
        "state": "ID",
        "airport_count": 5
      },
      {
        "state": "OR",
        "airport_count": 3
      },
      {
        "state": "MT",
        "airport_count": 2
      }
    ]
  }
]
WITH __stage0 AS (
  SELECT
    group_set,
    CASE WHEN group_set IN (0,1,2) THEN
      airports.faa_region
      END as faa_region__0,
    CASE WHEN group_set=0 THEN
      COUNT( 1)
      END as airport_count__0,
    CASE WHEN group_set=1 THEN
      airports.state
      END as state__1,
    CASE WHEN group_set=1 THEN
      COUNT( 1)
      END as airport_count__1,
    CASE WHEN group_set=2 THEN
      airports.state
      END as state__2,
    CASE WHEN group_set=2 THEN
      COUNT( 1)
      END as airport_count__2
  FROM `malloy-data.faa.airports` as airports
  CROSS JOIN (SELECT row_number() OVER() -1  group_set FROM UNNEST(GENERATE_ARRAY(0,2,1)))
  WHERE ((group_set NOT IN (1) OR (group_set IN (1) AND (airports.state IS NOT NULL)
  AND (airports.fac_type='HELIPORT'))))
  AND ((group_set NOT IN (2) OR (group_set IN (2) AND (airports.state IS NOT NULL)
  AND (airports.fac_type='SEAPLANE BASE'))))
  GROUP BY 1,2,4,6
)
SELECT
  faa_region__0 as faa_region,
  ANY_VALUE(CASE WHEN group_set=0 THEN airport_count__0 END) as airport_count,
  ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(
    state__1 as state, 
    airport_count__1 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__1 desc ) as heliports,
  ARRAY_AGG(CASE WHEN group_set=2 THEN STRUCT(
    state__2 as state, 
    airport_count__2 as airport_count
    ) END IGNORE NULLS  ORDER BY  airport_count__2 desc ) as seaplane_bases
FROM __stage0
GROUP BY 1
ORDER BY 2 desc